<?php if (!defined('BASEPATH')) exit('No direct script access allowed'); 
Class Setting_m extends Model
{
	function Setting_m()
	{
		parent::Model();
		$this->load->helper('text');
	}
	
	function getDataDetail($id)
	{
		$w = $this->db->query("SELECT a.TITLE, a.ABSTRACT, DATE_FORMAT(a.DATE,'%d-%b-%y') as date
		FROM m_koleksi A
		where a.ID_COLLECTION = '".$id."'");
		return $w->result();
	}
	
	function getAuthor($id)
	{
		$this->db->select('nim_penulis,nama_penulis, jabatan_penulis');
		$this->db->where('id_collection', $id);
		return $this->db->get('penulis');
	}
	
	function getAdvisor($id)
	{
		$this->db->select('nip_pembimbing, nama_pembimbing,jabatan_pembimbing');
		$this->db->where('id_collection', $id);
		return $this->db->get('pembimbing');
	}
	
	function getCboThnAjrn()
	{
		$this->db->select('kd_tahun_ajaran, tahun_ajaran');
		$this->db->from('m_tahun_ajaran');
		$records=$this->db->get();
		// Mendefinisikan data yang ada pada table ke dalam array
		$data=array();
		foreach ($records->result() as $row)
			{
				$data[$row->kd_tahun_ajaran] = $row->tahun_ajaran;
			}
		return ($data);
	}
	function getCboGelar()
	{
		$this->db->select('kd_gelar, nama_gelar');
		$this->db->from('m_gelar');
		$records=$this->db->get();
		// Mendefinisikan data yang ada pada table ke dalam array
		$data=array();
		foreach ($records->result() as $row)
			{
				$data[$row->kd_gelar] = $row->nama_gelar;
			}
		return ($data);
	}
	function getCboDosen()
	{
		$this->db->select('nip, nama_pegawai');
		$this->db->from('m_pegawai');
		$records=$this->db->get();
		// Mendefinisikan data yang ada pada table ke dalam array
		$data=array();
		foreach ($records->result() as $row)
			{
				$data[$row->nip] = $row->nama_pegawai;
			}
		return ($data);
	}
	function getCboSubTypeCol()
	{
		$this->db->select('kd_sub_tipe_koleksi, nama_sub_koleksi');
		$this->db->from('m_sub_tipe_koleksi');
		$records=$this->db->get();
		// Mendefinisikan data yang ada pada table ke dalam array
		$data=array();
		foreach ($records->result() as $row)
			{
				$data[$row->kd_sub_tipe_koleksi] = $row->nama_sub_koleksi;
			}
		return ($data);
	}
	
	function getNewUpload($id)
	{
		$hasil = "";
		//mengirimkan query 
		$w = $this->db->query("SELECT a.TITLE, a.ABSTRACT, DATE_FORMAT(a.DATE,'%d %b %y') as date, g.nama_mahasiswa as nama_penulis, e.nama_pegawai,A.ID_COLLECTION, 
								f.nama_files, f.path_files, f.type_files, f.size_files
								FROM m_koleksi A
								left join PENULIS b on a.ID_COLLECTION = b.ID_COLLECTION
								left join pembimbing d on a.ID_COLLECTION = d.id_collection
								left join m_pegawai e on d.nip_pembimbing = e.NIP
								left join files f on a.id_collection = f.id_collection
								left join m_mahasiswa g on b.nim_penulis = g.nim
								where a.publish = '1' and a.approve = '1'
								ORDER BY A.DATE, A.DATE LIMIT 10");
		foreach($w->result() as $h)
		{	
			$hasil .= " <li class='ds-artifact-item odd'>
						<div class='artifact-description'>
						<div class='artifact-title'>".
						anchor('home/detail/'.$h->ID_COLLECTION, $h->TITLE)
						."</div>
						 <div class='artifact-info'>
						 <span class='author'><span>"
						.$h->nama_penulis. "</span>
						</span> <span class='publisher-date'>(<span class='date'>"
						.$h->date."</span>)</span>
						</div></div></li>";
						
		}
		//melemparkan data dari hasil query dan perulangan
		return $hasil;
	}
	// Bind for drop down list Jenis Koleksi
	function getCboJenisKoleksi()
	{
		$this->db->select('kd_type_koleksi, nama_type');
		$this->db->from('m_tipe_koleksi');
		$records=$this->db->get();
		// Mendefinisikan data yang ada pada table ke dalam array
		$data=array();
		foreach ($records->result() as $row)
			{
				$data[$row->kd_type_koleksi] = $row->nama_type;
			}
		return ($data);
	}
	// Bind for drop down list Jurusan
	function getCboJurusan()
	{
		$this->db->select('kd_jurusan, nama_jurusan');
		$this->db->from('m_jurusan');
		$records=$this->db->get();
		// Mendefinisikan data yang ada pada table ke dalam array
		$data=array();
		foreach ($records->result() as $row)
			{
				$data[$row->kd_jurusan] = $row->nama_jurusan;
			}
		return ($data);
	}
	// Bind for drop down list Fakultas
	function getCboFakultas()
	{
		$this->db->select('kd_fakultas, nama_fakultas');
		$this->db->from('m_fakultas');
		$records=$this->db->get();
		// Mendefinisikan data yang ada pada table ke dalam array
		$data=array();
		foreach ($records->result() as $row)
			{
				$data[$row->kd_fakultas] = $row->nama_fakultas;
			}
		return ($data);
	}
	
	function getCboPembimbing()
	{
		$this->db->select('nip, nama_pegawai');
		$this->db->from('m_pegawai');
		$records=$this->db->get();
		// Mendefinisikan data yang ada pada table ke dalam array
		$data=array();
		foreach ($records->result() as $row)
			{
				$data[$row->nip] = $row->nama_pegawai;
			}
		return ($data);
	}
	
	function getCboKeahlianDosen()
	{
		$this->db->select('kd_keahlian, nama_keahlian');
		$this->db->from('m_keahlian_dosen');
		$records=$this->db->get();
		// Mendefinisikan data yang ada pada table ke dalam array
		$data=array();
		foreach ($records->result() as $row)
			{
				$data[$row->kd_keahlian] = $row->nama_keahlian;
			}
		return ($data);
	}
	
	
	function getUploadFromTypeCollection()
	{
		$hasil = "";
		//mengirimkan query 
		$TypeCollection = $this->db->query("select count(a.kd_type_koleksi) as Total, c.NAMA_TYPE, a.KD_TYPE_KOLEKSI
		from m_koleksi a
		inner join m_jurusan b on a.KD_JURUSAN = b.KD_JURUSAN
		inner join m_tipe_koleksi c on a.KD_TYPE_KOLEKSI = c.KD_TYPE_KOLEKSI
		where a.publish = '1' and a.approve = '1'
		group by a.kd_type_koleksi, C.nama_type
		order by A.DATE limit 10");
		
		foreach($TypeCollection->result() as $h)
		{	
			$hasil .= "<tr>
							<td class='standardcommunity'> &nbsp;-&nbsp";
			$hasil .= anchor('home/DetailKoleksiPerTipeKoleksi/'.$h->KD_TYPE_KOLEKSI,$h->NAMA_TYPE .' ['.$h->Total.']');
			$hasil .= "</td></tr>";
		}
		//melemparkan data dari hasil query dan perulangan
		return $hasil;
	}
	
	function SavedCollection($Title, $Abstract, $jnsKoleksi, $noAuthor, $nameAuthor, $noAdvisor, $nameAdvisor, $jurusan, $tglRealese,$dataUpload,$subtipekol,$jabAuthor,$jabAdvisor)
	{
		$idKoleksi = $this->getIDKoleksi();
		$data = array(
			'id_collection' => $idKoleksi,
			'Title' => $Title,
			'Abstract' => $Abstract,
			'kd_type_koleksi' => $jnsKoleksi,
			'kd_jurusan' => $jurusan,
			'date' => $tglRealese,
			'kd_sub_tipe_koleksi' => $subtipekol
		);
		$this->db->insert('m_koleksi', $data);
		$saveFiles = $this->doSavingFiles($dataUpload,$idKoleksi);
		$saveAuthor = $this->doSavingAuthor($idKoleksi, $noAuthor, $nameAuthor,$jabAuthor);
		$saveAdvisor = $this->doSavingAdvisor($idKoleksi, $noAdvisor, $nameAdvisor,$jabAdvisor);
		return true;
	}
	
	function getIDKoleksi()
	{
		$noId = 0;
		$id = $this->db->query("select ID_COLLECTION as id from m_koleksi order by id_collection desc limit 1");
		foreach($id->result() as $h)
		{
			$noId = $h->id;
			$noId = $noId + 1;
		}
		return $noId;
	}
	
	function doSavingAuthor($idKoleksi, $noAuthor, $nameAuthor,$jabAuthor)
	{
		$auth = array($noAuthor, $nameAuthor, $jabAuthor);
		
		for($i=0; (count($auth)-1) >= $i; $i++){
			$no = $auth[0][$i];
			$name = $auth[1][$i];
			$jab = $auth[2][$i];
			if ($no&&$name&&$jab)
			{
				$data = array(
					'nim_penulis' => $no,
					'urut_penulis' => $i,
					'nama_penulis' => $name,
					'id_collection' => $idKoleksi,
					'jabatan_penulis' => $jab);
				$this->db->insert('penulis', $data);
			}
		}
	
	}
	
	function doSavingAdvisor($idKoleksi, $noAdvisor, $nameAdvisor,$jabAdvisor)
	{
		
		$auth = array($noAdvisor, $nameAdvisor, $jabAdvisor);
		
		for($i=0; (count($auth)-1) >= $i; $i++){
			$no = $auth[0][$i];
			$name = $auth[1][$i];
			$jab = $auth[2][$i];
			if ($no&&$name&&$jab)
			{
				$data = array(
					'urut_pembimbing' => $i,
					'id_collection' => $idKoleksi,
					'nip_pembimbing' => $no,
					'nama_pembimbing' => $name,
					'jabatan_pembimbing' =>$jab);
				$this->db->insert('pembimbing', $data);
			}
		}
	}
	
	function doSavingFiles($data, $noId)
	{
		$pathfiles = base_url();
		$pathfiles .= "uploads/";
		// $pathfiles .= $data['file_name'];
		$i = 0;
		foreach($data as $val)
		{
			$pathfiles .= $val['file_name'];
			$dataField = array(
			'id_collection' => $noId,
			'urut_files' => $i,
			'nama_files' => $val['file_name'],
			'path_files' => $pathfiles,
			'type_files' => $val['file_ext'],
			'size_files' => $val['file_size']);
			$this->db->insert('files', $dataField);
			$i++;
		}
		// $dataField = array(
			// 'id_files' => $noId,
			// 'nama_files' => $data['file_name'],
			// 'path_files' => $pathfiles,
			// 'type_files' => $data['file_ext'],
			// 'size_files' => $data['file_size']);
		// $this->db->insert('files', $dataField);
	}
	
	function checkingRegister($username, $password, $email, $nim)
	{
		$this->db->select('username');
		$this->db->where('username',$username);
		$this->db->from('zusers');
		$any = $this->db->get();
		if ($any->num_rows() > 1)
		{
			return 'Username Sudah Ada';
		}
		else
		{
			$flag = '';
			$resulta = $this->db->query("select nip, flag from (
									select nip, '3' as flag from m_pegawai 
									union all
									select nim, '4' as flag from m_mahasiswa ) as x
									where nip = '" .$nim. "'");
			if ($resulta->num_rows() == 1)
			{
				foreach($resulta->result() as $h)
				{	
					$flag = $h->flag;
				}
				$kdUser = $this->getKdUsers();
				//Definisikan field2 pada zusers ke dalam array
				$data = array(
					'id' => $nim,
					'kd_user' => $kdUser,
					'username' => $username,
					'password' => md5(md5(md5($password))),
					'aktif' => '0');
				//Saving ke table zusers
				$this->db->insert('zusers', $data);
				
				$data = array(
					'kd_akses' => $flag,
					'kd_user' => $kdUser,
					'username' => $username);
				//Saving ke table zusers
				$this->db->insert('zakses_user', $data);
				
				return 'Anda sudah berhasil registrasi tunggu approval dari admin';
			}
			else
			{
				return 'Nomor Induk Tidak Terdaftar Pada Master Data';
			}
		}
	}
	
	function getKdUsers()
	{
		$id = '0';
		$this->db->select('kd_user');
		$this->db->from('zusers');
		$this->db->order_by('kd_user','desc');
		$this->db->limit(1);
		$result = $this->db->get();
		if ($result->num_rows() == 1)
		{
			foreach($result->result() as $rec)
			{	
				$id = $rec->kd_user;
				$id = $id + 1;
			}
		}
		return $id;
	}
	
	function get_ten_koleksiSearch($limit, $offseta,$query)
	{
		// $this->db->select('DATE_FORMAT(DATE,"%b %y") as date,title');
		// $this->db->from('m_koleksi');
		if ($offseta == false)
		{
			$offseta = ', 0';
		}
		else{ $offseta = ','.$offseta;}
		$query = $this->db->query("select DATE_FORMAT(date,'%d-%b-%y') as date, title, id_collection 
		from m_koleksi where title like '%$query%' and approve = '1' and publish = '1' group by date, title, id_collection limit $limit ");
		// $this->db->limit($limit, $offset);
		return $query;
	}
	
	//Searching 1 criteria
	function getCollection1($ddl1,$query1)
	{
		$hasil = "";
		//mengirimkan query 
		$w = $this->db->query("SELECT a.Title, a.ABSTRACT, DATE_FORMAT(a.DATE,'%b %y') as dates, f.nama_mahasiswa as nama_penulis, e.nama_pegawai, A.ID_COLLECTION
						FROM m_koleksi A
						left join PENULIS b on a.ID_COLLECTION = b.ID_COLLECTION
						left join pembimbing d on a.ID_COLLECTION = d.id_collection
						left join m_pegawai e on d.nip_pembimbing = e.NIP 
						left join m_mahasiswa f on b.nim_penulis = f.nim
						WHERE A.approve = '1' and A.publish = '1' and $ddl1 LIKE '%" .$query1. "%'");
		foreach ($w->result() as $tbKol)
			{
				
				$hasil .= '<tr><td headers="t1" class="evenRowEvenCol" nowrap="nowrap" align="right">htf'.$tbKol->dates.'</td>';
				$hasil .= '<td headers="t2" class="evenRowOddCol">'.anchor('home/detail/'.$tbKol->ID_COLLECTION,$tbKol->Title).'</td></tr>';
			}
		
		if ( $w->num_rows() == 0)
		{
			$hasil = "Data tidak ditemukan";
		}
		
		//melemparkan data dari hasil query dan perulangan
		return $hasil;
	}
	
	function getCollectionPerNIP($NIP)
	{
		$hasil = "";
		//mengirimkan query 
		$w = $this->db->query("SELECT A.Title, DATE_FORMAT(A.DATE,'%d %b %y') as dates, f.nama_mahasiswa as nama_penulis, A.ID_COLLECTION, h.nama_type
						FROM m_koleksi A
						left join PENULIS b on a.ID_COLLECTION = b.ID_COLLECTION
						left join m_mahasiswa f on b.nim_penulis = f.nim
						left join files g on A.id_collection = g.id_collection
						inner join m_tipe_koleksi h on A.kd_type_koleksi = h.kd_type_koleksi
						WHERE B.NIM_PENULIS = '$NIP' and B.JABATAN_PENULIS = 'Penulis Utama'
						group by A.Title, A.DATE, f.nama_mahasiswa, A.ID_COLLECTION, h.nama_type");
		foreach ($w->result() as $tbKol)
			{
				$link = '';
				$query = $this->db->query("select a.nama_files 
						from files a
						inner join m_koleksi b on a.id_collection = b.id_collection
						inner join penulis c on a.id_collection = c.id_collection
						inner join m_mahasiswa d on c.nim_penulis = d.nim
						WHERE a.id_collection = '$tbKol->ID_COLLECTION' and c.JABATAN_PENULIS = 'Penulis Utama'");
				foreach ($query->result() as $Link)
				{
					$link .= "<a href=". base_url()."uploads/".$Link->nama_files.">".$Link->nama_files."</a> || ";
				}
				$hasil .= '<tr><td headers="t1" class="evenRowEvenCol" nowrap="nowrap" align="right">'.$tbKol->Title.'</td>';
				$hasil .= '<td headers="t2" class="evenRowEvenCol" nowrap="nowrap" align="right">'.$tbKol->nama_type.'</td>';
				$hasil .= '<td headers="t3" class="evenRowEvenCol" nowrap="nowrap" align="right">'.$tbKol->dates.'</td>';
				$hasil .= '<td headers="t4" class="evenRowEvenCol" nowrap="nowrap" align="right">'.$link.'</td>';
				$hasil .= '<td headers="t3" class="evenRowEvenCol" nowrap="nowrap" align="right">';
				// $hasil .= anchor("mahasiswa/updateKoleksi/".$tbKol->ID_COLLECTION,'Update');
				// $hasil .= ' ';
				$hasil .= anchor("mahasiswa/deleteKoleksi/".$tbKol->ID_COLLECTION,'Delete',array("class" => "Delete","onclick"=>"return confirm('Anda yakin akan menghapus data ini?')"));
				$hasil .= '</td></tr>';
			}
		
		if ( $w->num_rows() == 0)
		{
			$hasil = "Data tidak ditemukan";
		}
		
		//melemparkan data dari hasil query dan perulangan
		return $hasil;
	}
	
	function getCollection($queries, $order, $limit, $by)
	{
		if ($limit == '')
		{
			$limit = 10;
		}
		if ($order == '')
		{
			$order = 'A.TITLE, A.DATE';
		}
		
		$hasil = "";
		//mengirimkan query 
		$w = $this->db->query("SELECT a.TITLE, a.ABSTRACT, DATE_FORMAT(a.DATE,'%b %y') as date, g.nama_mahasiswa as nama_penulis, e.nama_pegawai, f.path_files, f.nama_files, f.size_files, f.type_files, A.ID_COLLECTION
						FROM m_koleksi A
						left join PENULIS b on a.ID_COLLECTION = b.ID_COLLECTION
						left join pembimbing d on a.ID_COLLECTION = d.id_collection
						left join m_pegawai e on d.nip_pembimbing = e.NIP 
						left join files f on a.id_collection = f.id_collection
						left join m_mahasiswa g on b.nim_penulis = g.nim
						WHERE A.approve = '1' and A.publish = '1' and A.TITLE LIKE '%$queries%' Order by $order $by limit $limit");
		//melemparkan data dari hasil query dan perulangan
		return $w;
	}
	//Searching customize criteria 1
	function getCollectionCustome($queries,$TipeFilter,$txtFilter)
	{
		$hasil = "";
		
		$query = "SELECT a.TITLE, a.ABSTRACT, DATE_FORMAT(a.DATE,'%b %y') as date, g.nama as nama_penulis, e.nama, f.path_files, f.nama_files, f.size_files, f.type_files, A.ID_COLLECTION
						FROM m_koleksi A
						left join PENULIS b on a.ID_COLLECTION = b.ID_COLLECTION
						left join pembimbing d on a.ID_COLLECTION = d.id_collection
						left join m_pegawai e on d.nip_pembimbing = e.NIP 
						left join files f on a.id_collection = f.id_collection
						left join m_mahasiswa g on b.nim_penulis = g.nim
						WHERE A.approve = '1' and A.publish = '1' and A.TITLE LIKE '%" .$queries. "%' and ".$TipeFilter. " like '%" .$txtFilter. "%'";
		
		//mengirimkan query 
		$w = $this->db->query($query);
		foreach($w->result() as $h)
		{	
			$abstract = $h->ABSTRACT;
			$abstract = character_limiter($abstract, 50);
							
			$hasil .= "<li class='ds-artifact-item odd'>
						<div class='artifact-description'>
						<div class='artifact-title'>
							<a href='#'>".anchor('home/detail/'.$h->ID_COLLECTION, $h->TITLE)."</a>
							<span class='Z3988' title='#'></span>
							</div>
							<div class='artifact-info'>
							<span class='author'>
							<span>".$h->nama_penulis."</span>
							</span> <span class='publisher-date'><span class='date'>".$h->date."</span></span></div>
							<div class='artifact-abstract'></div>
							</div>
							</li>
							<li class='ds-artifact-item even'>
							</li>";
		}
		
		if ( $w->num_rows() == 0)
		{
			$hasil = "Data tidak ditemukan";
		}
		
		//melemparkan data dari hasil query dan perulangan
		return $hasil;
	}
	//Lookup data yang baru register untuk admin
	function getNewMember()
	{
		$hasil = "";
		//mengirimkan query 
		$w = $this->db->query("SELECT A.USERNAME, B.ID, X.NAMA, 
					CASE WHEN X.FLAG = 1 THEN 'Mahasiswa' ELSE
					CASE WHEN X.FLAG = 2 THEN 'Dosen' ELSE 'Admin' END END AS KETERANGAN,
					C.HAK_AKSES, A.AKTIF
					FROM zusers A
					INNER JOIN zgroups B ON A.ID_GROUP = B.ID_GROUP
					CROSS JOIN ( SELECT NIM, NAMA, 1 AS FLAG FROM m_mahasiswa
							UNION ALL
							SELECT NIP, NAMA, 2 AS FLAG FROM m_pegawai ) X
							ON B.ID = X.NIM
					INNER JOIN HAK_AKSES C ON A.KD_AKSES = C.KD_AKSES
					WHERE A.AKTIF = '0' LIMIT 10");
		foreach($w->result() as $h)
		{	
			$hasil .= '<tr>
						<td align="center">
							' .$h->USERNAME.'
						</td>
						<td align="center">
							' .$h->ID.'
						</td>
						<td align="center">
							' .$h->ID.'
						</td>
						<td align="center">
							' .$h->KETERANGAN.'
						</td>
						<td align="center">
							' .$h->HAK_AKSES.'
						</td>
						<td align="center">
							' .$h->AKTIF.'
						</td>
					   </tr>';
		}
		//melemparkan data dari hasil query dan perulangan
		return $hasil;
	}
	//Lookup data semua jurusan dimasukan ke dalam bentuk table
	function bindDataJurusan()
	{
		$data = '';
		$this->db->select('kd_jurusan, name_jurusan');
		$this->db->from('m_jurusan');
		$records=$this->db->get();
		// if ($records->num_rows() => 1)
		// {
			foreach ($records->result() as $row)
			{
				$data .="<tr>
							<td>
								" .$row->kd_jurusan."
							</td>
							<td>
								" .$row->name_jurusan."
							</td>
							<td>
								
							</td>
						</tr>";
			}
		// }
		return $data;
	}
	//Lookup data semua tipe koleksi dimasukan ke dalam bentuk table
	function bindDataTypeKoleksi()
	{
		$data = ' ';
		$this->db->select('kd_type_koleksi, nama_type');
		$this->db->from('m_tipe_koleksi');
		$records=$this->db->get();
		// if ($records->num_rows() => 1)
		// {
			foreach ($records->result() as $row)
			{
				$data .="<tr>
							<td>
								" .$row->kd_type_koleksi."
							</td>
							<td>
								" .$row->nama_type."
							</td>
							<td>
								
							</td>
						</tr>";
			}
		// }
		return $data;
	}
	//browse berdasarkan nama penulis
	function BrowseForAuthor($limit=10, $sort, $query, $by)
	{
		if ($query==""){
			$query = " where b.nama_mahasiswa like '%$query%'";
		}else{$query == "";}
		if ($sort==""){
			$sort == " ";
		}
		else{$sort = " where b.nama_mahasiswa like '$sort%'";}
		if($by){
		$by = 'order by b.nama_mahasiswa $by';}
		else{$by = 'order by b.nama_mahasiswa asc';}
		
		$result = $this->db->query("select a.nim_penulis as nim, b.nama_mahasiswa as nama
		from penulis a
		inner join m_mahasiswa b on a.nim_penulis = b.nim
		$query $sort
		group by a.nim_penulis, b.nama_mahasiswa limit $limit
		");
		return $result;
	}
	//browse berdasarkan nama Pembimping
	function BrowseForAdvisor($limit=10, $sort, $query, $by)
	{
		if ($query==""){
			$query = " where a.nama_pembimbing like '%$query%'";
		}else{$query == "";}
		if ($sort==""){
			$sort == " ";
		}
		else{$sort = " where a.nama_pembimbing like '$sort%'";}
		if($by){
		$by = 'order by b.nama_pembimbing $by';}
		else{$by = 'order by b.nama_pembimbing asc';}
		
		$result = $this->db->query("select a.nip_pembimbing as nip, a.nama_pembimbing as nama
		from pembimbing a
		$query $sort
		group by a.nip_pembimbing, a.nama_pembimbing limit $limit
		");
		return $result;
	}
	
	function DetailKoleksiPenulis($nim)
	{
		$result = $this->db->query("select a.id_collection, a.title, c.nama_mahasiswa as nama_penulis, e.nama_pegawai as nama_pembimbin, DATE_FORMAT(date,'%b %y') as date
		from m_koleksi a 
		left join penulis b on a.id_collection = b.id_collection
		left join m_mahasiswa c on b.nim_penulis = c.nim
		left join pembimbing d on a.id_collection = d.id_collection
		left join m_pegawai e on d.nip_pembimbing = e.nip
		where b.nim_penulis = $nim ");
		return $result;
	}
	
	function DetailKoleksiPembimbing($nip)
	{
		$result = $this->db->query("select a.id_collection, a.title, c.nama_mahasiswa as nama_penulis, e.nama_pegawai as nama_pembimbin, DATE_FORMAT(date,'%b %y') as date
		from m_koleksi a 
		left join penulis b on a.id_collection = b.id_collection
		left join m_mahasiswa c on b.nim_penulis = c.nim
		left join pembimbing d on a.id_collection = d.id_collection
		left join m_pegawai e on d.nip_pembimbing = e.nip
		where d.nip_pembimbing = $nip ");
		return $result;
	}
	
	function DetailKoleksiPerTipe($kd)
	{
		$result = $this->db->query("select a.id_collection, a.title, c.nama_mahasiswa as nama_penulis, e.nama_pegawai as nama_pembimbin, DATE_FORMAT(date,'%b %y') as date
		from m_koleksi a 
		inner join m_tipe_koleksi f on a.kd_type_koleksi = f.kd_type_koleksi 
		left join penulis b on a.id_collection = b.id_collection
		left join m_mahasiswa c on b.nim_penulis = c.nim
		left join pembimbing d on a.id_collection = d.id_collection
		left join m_pegawai e on d.nip_pembimbing = e.nip
		where a.kd_type_koleksi = '$kd' and a.publish = '1' and a.approve = '1'
		group by a.id_collection, a.title,  date
		");
		return $result;
	}
	
	function deleteKoleksi($id)
	{
		$this->db->where('id_collection', $id);
		$this->db->delete('penulis');
		
		$this->db->where('id_collection', $id);
		$this->db->delete('pembimbing');
		
		$this->db->where('id_collection', $id);
		$this->db->delete('files');
		
		$this->db->where('id_collection', $id);
		$this->db->delete('m_koleksi');
		
		return true;
	}
	
	function getFiles($id)
	{
		$this->db->select('nama_files, path_files, type_files, size_files');
		$this->db->where('id_collection', $id);
		$this->db->from('files');
		// $this->db->order('urut_files', 'asc');
		return $this->db->get();
	}
	
}
